﻿Imports System.Configuration
Public Class acceso_a_datos
    Enum motores
        oracle
        sqlserver
        access
    End Enum
    Dim conexion As New Object
    Dim cmd As New Object
    'Dim co As String = "Data Source=Guille-Note;Initial Catalog=CEP_base_de_datos1;Integrated Security=True"
    Dim co As String = ConfigurationManager.ConnectionStrings("Conex").ConnectionString 'saca la cadena de conexion del aap.config
    'las siguientes dos lineas son por defecto el tipo de motro y la coneccion
    Dim motor As motores = motores.access
    Dim sting_conexion As String = co
    '--------------------------------------------------------------------------
    Dim tabla As String = "" 'esta sera la tabla con la que se va a trabajar en ese momento

    'Las declaro publica////////////////////
    Public Property _CadenaConexion() As String
        Get
            Return Me.sting_conexion
        End Get
        Set(ByVal value As String)
            Me.sting_conexion = value
        End Set
    End Property

    Public Property _tabla() As String
        Get
            Return Me.tabla
        End Get
        Set(ByVal value As String)
            Me.tabla = value
        End Set
    End Property
    '//////////////////////////////////////

    'CONSTRUCTORES////////////////////////
    'SIN PARAMETROS----------------------
    Public Sub New()
        Select Case Me.motor
            Case motores.access
                conexion = New Data.OleDb.OleDbConnection
                cmd = New Data.OleDb.OleDbCommand
            Case motores.sqlserver
                conexion = New Data.SqlClient.SqlConnection
                cmd = New Data.SqlClient.SqlCommand
        End Select
    End Sub
    'CONEXION Y MOTOR--------------------
    Public Sub New(ByVal _CadenaConexion As String, ByVal _MotorBaseDatos As motores)
        Me.sting_conexion = _CadenaConexion
        Me.motor = _MotorBaseDatos
        Select Case Me.motor
            Case motores.access
                conexion = New Data.OleDb.OleDbConnection
                cmd = New Data.OleDb.OleDbCommand
            Case motores.sqlserver
                conexion = New Data.SqlClient.SqlConnection
                cmd = New Data.SqlClient.SqlCommand
        End Select
    End Sub
    'CON TABLA-------------------------
    Public Sub New(ByVal _CadenaConexion As String, ByVal _NombreTabla As String, ByVal _MotorBaseDatos As motores)
        Me.sting_conexion = _CadenaConexion
        Me.tabla = _NombreTabla
        Me.motor = _MotorBaseDatos
        Select Case Me.motor
            Case motores.access
                conexion = New Data.OleDb.OleDbConnection
                cmd = New Data.OleDb.OleDbCommand
            Case motores.sqlserver
                conexion = New Data.SqlClient.SqlConnection
                cmd = New Data.SqlClient.SqlCommand
        End Select
    End Sub
    'SIN CONEXION (usando la public property)-------------
    Public Sub New(ByVal _MotorBaseDatos As motores, ByVal _NombreTabla As String)
        Me.tabla = _NombreTabla
        Me.motor = _MotorBaseDatos
        Select Case Me.motor
            Case motores.access
                conexion = New Data.OleDb.OleDbConnection
                cmd = New Data.OleDb.OleDbCommand
            Case motores.sqlserver
                conexion = New Data.SqlClient.SqlConnection
                cmd = New Data.SqlClient.SqlCommand
        End Select
    End Sub
    'SIN CONEXION NI TABLA (usar las public property)-------
    Public Sub New(ByVal _MotorBaseDatos As motores)
        Me.motor = _MotorBaseDatos
        Select Case Me.motor
            Case motores.access
                conexion = New Data.OleDb.OleDbConnection
                cmd = New Data.OleDb.OleDbCommand
            Case motores.sqlserver
                conexion = New Data.SqlClient.SqlConnection
                cmd = New Data.SqlClient.SqlCommand
        End Select
    End Sub
    '/////////////////////////////////////////////////////////



    '//ESTRUCTURA PARA CONECTAR BD///////////////////////////
    Private Sub _conectar()
        Me.conexion.ConnectionString = Me.sting_conexion 'transfiere el string
        Me.conexion.Open() 'abre la coneccion
        Me.cmd.Connection = conexion 'asigna la conecion al comando
        Me.cmd.CommandType = CommandType.Text 'le asigna el tipo de comando
    End Sub
    '////////////////////////////////////////////////////////

    '//Funcion que permite leer tabla conforme a la tabla asignada/////////
    '
    Public Function leo_tabla() As Data.DataTable
        Return Me._consulta("select * from " & Me.tabla)
    End Function
    '
    Public Function leo_tabla(ByVal pk As String, ByVal descripcion As String) As Data.DataTable
        Return Me._consulta("select " & pk & ", " & descripcion & " from " & Me.tabla)
    End Function
    '
    Public Function leo_tabla(ByVal restriccion As String) As Data.DataTable
        Return Me._consulta("select * from " & Me.tabla & " Where " & restriccion)
    End Function
    '/////////////////////////////////////////////////////////////////////////

    '//Funcion que recibe un comando para hacer una modificacion en particular////
    Public Sub _modicar(ByVal comando As String)
        Me._conectar()
        Me.cmd.CommandText = comando
        cmd.ExecuteNonQuery() 'No trae datos, solo actualiza
        Me.conexion.Close()
    End Sub
    '///////////////////////////////////////////////////////////////////////////////


    Public Function _consulta(ByVal comando As String) As Data.DataTable
        Dim _tabla As New Data.DataTable
        Me._conectar()
        Me.cmd.CommandText = comando
        _tabla.Load(Me.cmd.ExecuteReader())
        Me.conexion.Close()
        Return _tabla
    End Function

    Public Sub _borrar(ByVal comando As String)
        Me._conectar()
        Me.cmd.CommandText = comando
        cmd.ExecuteNonQuery()
        Me.conexion.Close()
    End Sub


    Public Function _insertar(ByVal valores As String) As Boolean
        Dim txt_Insert As String = ""
        txt_Insert = Me.armo_insert()
        txt_Insert += Me.asigno_valores_insert(valores)
        Me._conectar()
        Me.cmd.CommandText = txt_Insert
        cmd.ExecuteNonQuery()
        Me.conexion.Close()
        Return True
    End Function

    Private Function armo_insert() As String
        Dim tabla As Data.DataTable
        Dim contador As Integer = 0
        Dim txt As String = ""
        tabla = Me.leo_tabla()
        txt = "insert into " & Me.tabla & " ("
        While tabla.Columns.Count() > contador
            txt += tabla.Columns(contador).Caption 'te devuelve el nombre de la columna
            contador += 1
            If contador < tabla.Columns.Count() Then
                txt += ", "
            End If
        End While
        txt += ") values ("
        Return txt
    End Function
    Private Function asigno_valores_insert(ByVal txt_insertar As String) As String
        'tabla que contiene la estructura en la base
        Dim objeto_tabla As Data.DataTable
        Dim contador As Integer = 0 'contador de bucle
        Dim campo As Integer = 0 'ubicación del campo
        Dim coma As Integer = 0 'ubicacion de la ","
        Dim igual As Integer = 0 'ubicación del "="
        Dim txt_origen As String = "" 'texto origen
        Dim nombre_campo As String = "" 'campo a buscar
        Dim valor As String = "" 'valor del campo
        Dim txt As String = "" 'texto que se va formando para el insert
        Dim tipo_dato As String = ""
        objeto_tabla = Me.leo_tabla()
        txt_origen = txt_insertar.ToUpper.Trim()
        While objeto_tabla.Columns.Count() > contador
            nombre_campo = objeto_tabla.Columns(contador).Caption.ToUpper.Trim()
            tipo_dato = objeto_tabla.Columns(contador).DataType.ToString
            campo = txt_origen.IndexOf(nombre_campo)
            If campo <> -1 Then
                igual = txt_origen.IndexOf("=", campo)
                coma = txt_origen.IndexOf(",", campo)
                If coma <> -1 Then
                    valor = Me.acomodo_al_tipo_dato(txt_origen.Substring(igual + 1, coma - igual - 1), tipo_dato)
                Else
                    valor = Me.acomodo_al_tipo_dato(txt_origen.Substring(igual + 1), tipo_dato)
                End If
            Else
                igual = -1
                coma = -1
                valor = "null"
            End If
            txt += valor
            contador += 1
            If contador < objeto_tabla.Columns.Count() Then
                txt += ", "
            End If
        End While
        txt += ")"
        Return txt
    End Function

    Private Function acomodo_al_tipo_dato(ByVal texto As String, ByVal tipo_dato As String) As String
        Select Case tipo_dato
            Case "System.String"
                Return "'" & texto & "'"
            Case "System.Int16"
                Return texto
            Case "System.Int32"
                Return texto
            Case "System.Int64"
                Return texto
            Case "System.Byte"
                Return texto
            Case "System.Double"
                Return texto
            Case "System.Decimal"
                Return texto
            Case "System.DateTime"
                If motor = motores.access Then
                    Return "#" & texto & "#"
                Else
                    Return "'" & texto & "'"
                End If
            Case "System.DateTimeKind"
                If motor = motores.access Then
                    Return "#" & texto & "#"
                Else
                    Return "'" & texto & "'"
                End If
            Case Else
                Return texto
        End Select
    End Function



    '//////////AGREGAR ESTA FUNCION PARA QUE ANDE EL REPORTE
    Public Sub _consulta(ByVal comando As String, ByRef _tabla As Data.DataTable)
        Me._conectar()
        Me.cmd.CommandText = comando
        _tabla.Load(Me.cmd.ExecuteReader())
        Me.conexion.Close()
    End Sub
End Class
